﻿CREATE TABLE [CA].[Documents] (
    [Id]                  INT           NOT NULL,
    [DepartmentId]        AS            ([Id]/(100000000)),
    [DocTypeId]           INT           CONSTRAINT [DF_Documents_DocTypeId] DEFAULT ((1)) NOT NULL,
    [ParentId]            INT           NULL,
    [DocDate]             SMALLDATETIME CONSTRAINT [DF_Documents_DocDate] DEFAULT (getdate()) NOT NULL,
    [DocNumber]           VARCHAR (20)  CONSTRAINT [DF_Documents_DocNumber] DEFAULT ((1)) NOT NULL,
    [ContragentId]        INT           CONSTRAINT [DF_Documents_ContragentId] DEFAULT ((0)) NULL,
    [ContragentDocDate]   SMALLDATETIME NULL,
    [ContragentDocNumber] VARCHAR (20)  NULL,
    [PaymentDate]         SMALLDATETIME NULL,
    [CashRegisterShiftId] INT           NULL,
    [DepartmentUserId]    INT           NULL,
    [ShiftDepartmentId]   INT           NULL,
    [Note]                VARCHAR (250) NULL,
    [MD]                  DATETIME      CONSTRAINT [DF__Documents__MD__33F4B129] DEFAULT (getdate()) NOT NULL,
    [MW]                  VARCHAR (50)  CONSTRAINT [DF__Documents__MW__34E8D562] DEFAULT (suser_sname()) NOT NULL,
    [СashlessPayment]     BIT           NULL,
    CONSTRAINT [PK_Documents] PRIMARY KEY NONCLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80),
    CONSTRAINT [FK_Documents_CashRegisterShifts] FOREIGN KEY ([CashRegisterShiftId]) REFERENCES [CD].[CashRegisterShifts] ([Id]),
    CONSTRAINT [FK_Documents_Contragents] FOREIGN KEY ([ContragentId]) REFERENCES [CA].[Contragents] ([Id]),
    CONSTRAINT [FK_Documents_DepartmentUsers] FOREIGN KEY ([DepartmentUserId]) REFERENCES [CD].[DepartmentUsers] ([Id]) ON UPDATE CASCADE,
    CONSTRAINT [FK_Documents_DocTypes] FOREIGN KEY ([DocTypeId]) REFERENCES [CO].[DocTypes] ([Id]) ON UPDATE CASCADE NOT FOR REPLICATION
);


GO
CREATE NONCLUSTERED INDEX [IX_Documents_DocTypeId]
    ON [CA].[Documents]([DocTypeId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Documents_CashRegisterShiftId]
    ON [CA].[Documents]([CashRegisterShiftId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Documents_ContragentdId]
    ON [CA].[Documents]([ContragentId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Documents_DocDate]
    ON [CA].[Documents]([DocDate] ASC)
    INCLUDE([Id]);


GO
CREATE NONCLUSTERED INDEX [IX_Documents_ParentId]
    ON [CA].[Documents]([ParentId] DESC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Documents_DepartmentId]
    ON [CA].[Documents]([DepartmentId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Documents_MD]
    ON [CA].[Documents]([MD] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [dta_index_Documents_5_1789965453__K2_K3_K7_K5_1_6_8_9_14_15]
    ON [CA].[Documents]([DepartmentId] ASC, [DocTypeId] ASC, [ContragentId] ASC, [DocDate] ASC)
    INCLUDE([Id], [DocNumber], [ContragentDocDate], [ContragentDocNumber], [Note], [MD]);


GO
CREATE STATISTICS [dta_stat_1789965453_7_2_3_5]
    ON [CA].[Documents]([ContragentId], [DepartmentId], [DocTypeId], [DocDate]);


GO
CREATE STATISTICS [dta_stat_1789965453_7_3]
    ON [CA].[Documents]([ContragentId], [DocTypeId]);


GO
CREATE STATISTICS [dta_stat_1789965453_5_3_2]
    ON [CA].[Documents]([DocDate], [DocTypeId], [DepartmentId]);


GO
CREATE STATISTICS [dta_stat_1789965453_3_7_5]
    ON [CA].[Documents]([DocTypeId], [ContragentId], [DocDate]);


GO

CREATE TRIGGER CA.DocumentsDeleteLog ON CA.Documents AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

INSERT INTO [PR].[DocDeleteLog] (TableName, IdValue, MD, MW)
SELECT 'CA.Documents', Id, getdate(), SYSTEM_USER FROM DELETED

END

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Код подразделения. Вычисляется из идентификатора', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'DepartmentId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Для заказов - ссылка на сводную заявку', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'ParentId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Возврат от покупателя - дата чека', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'ContragentDocDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Возврат от покупателя - номер чека', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'ContragentDocNumber';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Для приходных накладных - дата оплаты накладной поставщика', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'PaymentDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'ID смены кассы для чеков', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'CashRegisterShiftId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Для чеков - Id продавца-кассира', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'DepartmentUserId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Код подразделения при перемещении товаров внутри организации. Для чеков - тип скидки (CO.SaleDiscountTypes.Id)', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'ShiftDepartmentId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Для чеков - признак безналичной оплаты', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Documents', @level2type = N'COLUMN', @level2name = N'СashlessPayment';

